package com.crm.dao.impl;

import java.util.List;
import java.util.Map;

import com.crm.dao.CustomerDao;
import com.crm.pojo.Customer;
import com.crm.pojo.PageInfo;
import com.crm.util.JdbcUtil;

public class CustomerDaoImpl implements CustomerDao {

	@Override
	public List<Map> queryDict(String id) {
		String sql = "select * from base_dict where dict_type_code=?";
		return JdbcUtil.executeQuery(sql, id);
	}

	@Override
	public int insertCustomer(Customer customer) {
		String sql = "insert into cst_customer(cust_name,cust_source,cust_industry,cust_level,cust_phone,cust_mobile) values(?,?,?,?,?,?)";
		String custName = customer.getCustName();
		String custSource = customer.getCustSource();
		String custIndustry = customer.getCustIndustry();
		String custLevel = customer.getCustLevel();
		String custPhone = customer.getCustPhone();
		String custMobile = customer.getCustMobile();
		int n = JdbcUtil.executeUpdate(sql, custName, custSource, custIndustry, custLevel, custPhone, custMobile);
		return n;
	}

	@Override
	public List<Map> queryCustomer(String custName) {
		String sql = "select * from cst_customer where cust_name=?";
		return JdbcUtil.executeQuery(sql, custName);
	}

	@Override
	public int insertCustomerDetail(Integer custIds,Customer customer) {
		String sql = "insert into cst_customer_detail values(?,?,?,?,?,?,?,?,?,?,?,?)";
		Integer custId = custIds;
		String custRegion = customer.getCustRegion();
		String custZip = customer.getCustZip();
		String custaddress = customer.getCustAddress();
		String custFax = customer.getCustFax();
		String custWebsite = customer.getCustWebsite();
		String custLicence = customer.getCustLicence();
		String custCorporation = customer.getCustCorporation();
		Integer custCapital = customer.getCustCapital();
		String custBank = customer.getCustBank();
		String custPic = customer.getCustPic();
		String custMemo = customer.getCustMemo();
		int n = JdbcUtil.executeUpdate(sql, custId, custRegion, custZip, custaddress, custFax, custWebsite, custLicence, custCorporation, custCapital, custBank, custPic, custMemo);
		return n;
	}

	@Override
	public PageInfo queryCustomer(String custName, Integer pageSize, Integer pageNumber) {
		PageInfo page = new PageInfo();
		page.setPageSize(pageSize);
		page.setPageNumber(pageNumber);
		String sql = "select a.cust_id,cust_name,cust_source,cust_industry,cust_level,cust_phone,cust_mobile,cust_region,cust_zip,cust_address,cust_fax,cust_website,cust_licence,cust_corporation,cust_capital,cust_bank,cust_pic,cust_memo ";
		sql+="from cst_customer a left join cst_customer_detail b on a.cust_id=b.cust_id where 1=1";
		if(custName!=null && !"".equals(custName)) {
			sql += " and cust_name like '%"+custName+"%'";
		}
		page.setTotalCount(getTotalCount(sql));
		sql += " limit "+(pageNumber-1)*pageSize+","+pageSize;
		List<Map> customer = JdbcUtil.executeQuery(sql, null);
		page.setResult(customer);
		return page;
	}

	private Integer getTotalCount(String sql) {
		String newSql = "select count(*) from ("+sql+") c";
		List<Map> counts = JdbcUtil.executeQuery(newSql, null);
		String count = "";
		for(Map c:counts) {
			count = c.get("count(*)").toString();
		}
		return Integer.parseInt(count);
	}

	@Override
	public int deleteCustomer(Integer custId) {
		String sql1 = "delete from cst_customer where cust_id=?";
		String sql2 = "delete from cst_customer_detail where cust_id=?";
		int n1 = JdbcUtil.executeUpdate(sql1, custId);
		int n2 = 0;
		if(n1==1) {
			n2 = JdbcUtil.executeUpdate(sql2, custId);
		}
		return n2;
	}

	@Override
	public List<Map> quertCustomerByCustId(Integer custId) {
		String sql = "select a.cust_id,cust_name,cust_source,cust_industry,cust_level,cust_phone,cust_mobile,cust_region,cust_zip,cust_address,cust_fax,cust_website,cust_licence,cust_corporation,cust_capital,cust_bank,cust_pic,cust_memo ";
		sql+="from cst_customer a left join cst_customer_detail b on a.cust_id=b.cust_id where a.cust_id=?";
		return JdbcUtil.executeQuery(sql, custId);
	}

	@Override
	public int updateCustomer(Customer customer) {
		String sql = "update cst_customer set cust_name=?,cust_source=?,cust_industry=?,cust_level=?,cust_phone=?,cust_mobile=? where cust_id=?";
		String custName = customer.getCustName();
		String custSource = customer.getCustSource();
		String custIndustry = customer.getCustIndustry();
		String custLevel = customer.getCustLevel();
		String custPhone = customer.getCustPhone();
		String custMobile = customer.getCustMobile();
		Integer custId = customer.getCustId();
		int n = JdbcUtil.executeUpdate(sql, custName, custSource, custIndustry, custLevel, custPhone, custMobile,custId);
		return n;
	}

	@Override
	public int updateCustomerDetail(Customer customer) {
		String sql = "update cst_customer_detail set cust_region=?,cust_zip=?,cust_address=?,cust_fax=?,cust_website=?,cust_licence=?,cust_corporation=?,cust_capital=?,cust_bank=?,cust_pic=?,cust_memo=? where cust_id=?";
		Integer custId = customer.getCustId();
		String custRegion = customer.getCustRegion();
		String custZip = customer.getCustZip();
		String custaddress = customer.getCustAddress();
		String custFax = customer.getCustFax();
		String custWebsite = customer.getCustWebsite();
		String custLicence = customer.getCustLicence();
		String custCorporation = customer.getCustCorporation();
		Integer custCapital = customer.getCustCapital();
		String custBank = customer.getCustBank();
		String custPic = customer.getCustPic();
		String custMemo = customer.getCustMemo();
		int n = JdbcUtil.executeUpdate(sql, custRegion, custZip, custaddress, custFax, custWebsite, custLicence, custCorporation, custCapital, custBank, custPic, custMemo, custId);
		return n;
	}

	@Override
	public List<Map> queryCustomer() {
		String sql = "select * from cst_customer";
		return JdbcUtil.executeQuery(sql, null);
	}


}
